# !/usr/bin/python3
# _*_ coding: utf-8 _*_
# @Time     : 2022/3/7 上午11:20
# @Author   : HHR
# ___MSG___ : "IP爬虫"
import time, re, json, os, hashlib
import requests, pypinyin
import pythoncom
import comtypes.client
from PIL import Image
from operator import attrgetter
from docx import Document
from docx.oxml.ns import qn
from docx.shared import Cm, Pt, Inches
from docx.oxml import OxmlElement
from docx.enum.table import WD_CELL_VERTICAL_ALIGNMENT, WD_TABLE_ALIGNMENT
import time, json
from watermarker.marker import add_mark
from django.db.models import Q
from itertools import chain
from reportlab.lib.units import mm
from reportlab.pdfgen import canvas
from PyPDF4.pdf import PdfFileReader, PdfFileWriter
from django.db import connection, transaction

# 防止字符串乱码
os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.UTF8'


def set_cell_border(cell, **kwargs):
    tc = cell._tc
    tcPr = tc.get_or_add_tcPr()

    tcBorders = tcPr.first_child_found_in("w:tcBorders")
    if tcBorders is None:
        tcBorders = OxmlElement('w:tcBorders')
        tcPr.append(tcBorders)

    for edge in ('left', 'top', 'right', 'bottom', 'insideH', 'insideV'):
        edge_data = kwargs.get(edge)
        if edge_data:
            tag = 'w:{}'.format(edge)

            element = tcBorders.find(qn(tag))
            if element is None:
                element = OxmlElement(tag)
                tcBorders.append(element)

            for key in ["sz", "val", "color", "space", "shadow"]:
                if key in edge_data:
                    element.set(qn('w:{}'.format(key)), str(edge_data[key]))


def image_resize(img, width, height):
    """
    图片缩放
    :param img: 图片路径
    :param width: 宽
    :param height: 高
    :return: 宽高
    """
    image = Image.open(img)
    image.resize((width, height), Image.ANTIALIAS).save(img, quality=95, dpi=(72, 72))
    return width, height


def image_to_pdf(img, pdf_path=None, resize=True):
    """
    图片转PDF
    :param img: 图片路径
    :param pdf_path: 生成的PDF路径
    :param resize: 是否缩放图片至A4大小
    :return: None
    """
    pix_x, pix_y = 595, 842  # 分辨率为72像素时的A4纸大小
    if not pdf_path:
        pdf_path = f'{os.path.splitext(img)[0]}.pdf'
    w, h = Image.open(img).size
    if resize:
        w, h = image_resize(img, pix_x, pix_y)
        width = (pix_x - w) // 2
        height = (pix_y - h) // 2
        user = canvas.Canvas(pdf_path)
        if width > 1 and height > 1:  # 图片size是否小于最小值
            user.drawImage(img, width, height)  # PDF中居中显示
        else:
            user.drawImage(img, 0, 0)
    else:
        user = canvas.Canvas(pdf_path, pagesize=(w, h))
        user.drawImage(img, 0, 0)
    user.showPage()
    user.save()
    return pdf_path


class Jeremy_levin_preview():
    def levin_preview(self, item):
        if 'YZ/SJ' in item["audit_dic"]:
            docx = Document(os.getcwd() + "/static/superior_Jeremy/文电处理及阅文笺.docx")
            B_row = 14
            recive_number = 16
        else:
            docx = Document(os.getcwd() + "/static/superior_Jeremy/文电处理及阅文笺_副本.docx")
            B_row = 13
            recive_number = 15

        tables = docx.tables
        table = tables[0]
        tjr = table.cell(2, 1).paragraphs[0].add_run(item["department"])
        tjr.font.size = Pt(10)
        ks = table.cell(2, 3).paragraphs[0].add_run(item["apply_user"])
        ks.font.size = Pt(10)
        ytbh = table.cell(2, 5).paragraphs[0].add_run(item["recive_number"])
        ytbh.font.size = Pt(10)

        ytjs = table.cell(3, 1).paragraphs[0].add_run(item["send_company"])
        ytjs.font.size = Pt(10)

        ysje = table.cell(4, 1).paragraphs[0].add_run(item["send_number"])
        ysje.font.size = Pt(10)

        jfly = table.cell(5, 1).paragraphs[0].add_run(item["recive_time"])
        jfly.font.size = Pt(10)

        tjr = table.cell(6, 1).paragraphs[0].add_run(item["title"])
        tjr.font.size = Pt(10)
        ks = table.cell(7, 1).paragraphs[0].add_run(item["content"])
        ks.font.size = Pt(10)
        annotated = table.cell(8, 1).paragraphs[0].add_run(item["annotated_text"])
        annotated.font.size = Pt(10)
        limit = table.cell(9, 1).paragraphs[0].add_run(item["time_limit"])
        limit.font.size = Pt(10)
        remarks = table.cell(10, 1).paragraphs[0].add_run(item["remarks"])
        remarks.font.size = Pt(10)

        B_number = 0
        if "B" in item["audit_dic"]:
            for i in item["audit_dic"]["B"]:
                fgld = table.cell(B_row, 1).add_table(rows=2, cols=3)
                fgld.cell(0, 0).paragraphs[0].add_run(i['state'])
                fgld.cell(0, 1).merge(fgld.cell(0, 2))
                fgld.cell(0, 1).paragraphs[0].add_run(i['audit_note'])
                run = fgld.cell(1, 0).paragraphs[0].add_run()
                picture = run.add_picture(i['signature_picture'])
                picture.height = Cm(1.5)
                picture.width = Cm(3)
                fgld.cell(1, 2).paragraphs[0].add_run(i['audit_time'])
                fgld.cell(1, 2).vertical_alignment = WD_CELL_VERTICAL_ALIGNMENT.BOTTOM
                if B_number != len(item["audit_dic"]["B"]) - 1:
                    set_cell_border(fgld.rows[1].cells[0], bottom={"sz": 8, "val": "single", "color": "", "space": "0"})
                    set_cell_border(fgld.rows[1].cells[1], bottom={"sz": 8, "val": "single", "color": "", "space": "0"})
                    set_cell_border(fgld.rows[1].cells[2], bottom={"sz": 8, "val": "single", "color": "", "space": "0"})
                B_number += 1

        YB_DB_number = 0
        for t in item["audit_dic"]["YB"]:
            ybzr = table.cell(12, 1).add_table(rows=2, cols=3)
            ybzr.cell(0, 0).paragraphs[0].add_run(t['state'])
            ybzr.cell(0, 1).merge(ybzr.cell(0, 2))
            ybzr.cell(0, 1).paragraphs[0].add_run(t['audit_note'])
            zrrun = ybzr.cell(1, 0).paragraphs[0].add_run()
            zrpicture = zrrun.add_picture(t['signature_picture'])
            zrpicture.height = Cm(1.5)
            zrpicture.width = Cm(3)
            ybzr.cell(1, 2).paragraphs[0].add_run(t['audit_time'])
            ybzr.cell(1, 2).vertical_alignment = WD_CELL_VERTICAL_ALIGNMENT.BOTTOM
            if YB_DB_number != len(item["audit_dic"]["YB"]) - 1:
                set_cell_border(ybzr.rows[1].cells[0], bottom={"sz": 8, "val": "single", "color": "", "space": "0"})
                set_cell_border(ybzr.rows[1].cells[1], bottom={"sz": 8, "val": "single", "color": "", "space": "0"})
                set_cell_border(ybzr.rows[1].cells[2], bottom={"sz": 8, "val": "single", "color": "", "space": "0"})
            YB_DB_number += 1

        if 'YZ/SJ' in item["audit_dic"]:
            YZ_SJ_number = 0
            for j in item["audit_dic"]["YZ/SJ"]:
                yszr = table.cell(13, 1).add_table(rows=2, cols=3)
                yszr.cell(0, 0).paragraphs[0].add_run(j['state'])
                yszr.cell(0, 1).merge(yszr.cell(0, 2))
                yszr.cell(0, 1).paragraphs[0].add_run(j['audit_note'])
                ysrun = yszr.cell(1, 0).paragraphs[0].add_run()
                yspicture = ysrun.add_picture(j['signature_picture'])
                yspicture.height = Cm(1.5)
                yspicture.width = Cm(3)
                yszr.cell(1, 2).paragraphs[0].add_run(j['audit_time'])
                yszr.cell(1, 2).vertical_alignment = WD_CELL_VERTICAL_ALIGNMENT.BOTTOM
                if YZ_SJ_number != len(item["audit_dic"]["YZ/SJ"]) - 1:
                    set_cell_border(yszr.rows[1].cells[0], bottom={"sz": 8, "val": "single", "color": "", "space": "0"})
                    set_cell_border(yszr.rows[1].cells[1], bottom={"sz": 8, "val": "single", "color": "", "space": "0"})
                    set_cell_border(yszr.rows[1].cells[2], bottom={"sz": 8, "val": "single", "color": "", "space": "0"})
                YZ_SJ_number += 1

        result_number = 0
        for j in item["recive_people"]:
            if j["state"] == "已读":
                print('j["state"]: ', j)
                yszr = table.cell(recive_number, 1).add_table(rows=2, cols=3)
                yszr.cell(0, 0).paragraphs[0].add_run("已收阅")
                yszr.cell(0, 0).merge(yszr.cell(0, 2))
                if j['signature_picture']:
                    ysrun = yszr.cell(1, 0).paragraphs[0].add_run()
                    yspicture = ysrun.add_picture(j['signature_picture'])
                    yspicture.height = Cm(1)
                    yspicture.width = Cm(2)
                else:
                    yszr.cell(1, 0).paragraphs[0].add_run(j['user'])
                yszr.cell(1, 2).paragraphs[0].add_run(j['update_time'])
                yszr.cell(1, 2).vertical_alignment = WD_CELL_VERTICAL_ALIGNMENT.BOTTOM
                if result_number != len(item["recive_people"]) - 1:
                    set_cell_border(yszr.rows[1].cells[0], bottom={"sz": 8, "val": "single", "color": "", "space": "0"})
                    set_cell_border(yszr.rows[1].cells[1], bottom={"sz": 8, "val": "single", "color": "", "space": "0"})
                    set_cell_border(yszr.rows[1].cells[2], bottom={"sz": 8, "val": "single", "color": "", "space": "0"})
                result_number += 1

        data = str(item["recive_number"] + str(time.time()))
        file_hash = hashlib.sha1(data.encode("utf8")).hexdigest()
        path = os.getcwd() + f'/media/superior_Jeremy/wordfile/{file_hash}'
        if not os.path.exists(path):
            os.makedirs(path)
        docx.save(path + f"/文电处理及阅文笺.docx")
        pdf_path = os.getcwd() + f'/media/superior_Jeremy/pdf_file/{file_hash}'
        if not os.path.exists(pdf_path):
            os.makedirs(pdf_path)
        pythoncom.CoInitialize()
        word = comtypes.client.CreateObject("Word.Application")
        pythoncom.CoInitialize()
        word.Visible = 0
        print(pdf_path + f"/文电处理及阅文笺.pdf")
        newpdf = word.Documents.Open(path + f"/文电处理及阅文笺.docx")
        newpdf.SaveAs(pdf_path + f"/文电处理及阅文笺.pdf", FileFormat=17)
        newpdf.Close(pdf_path + f"/文电处理及阅文笺.pdf")
        return pdf_path + f"/文电处理及阅文笺.pdf"


class IP_spider():
    def __init__(self):
        self.capture_ip = "https://www.ip138.com/iplookup.asp"

    def get_ip(self):
        headers = {
            "Accept": "text/html,application/xhtml+xml,application/xml;q=0.9,image/avif,image/webp,image/apng,*/*;q=0.8,application/signed-exchange;v=b3;q=0.9",
            "Accept-Encoding": "gzip, deflate, br",
            "Accept-Language": "zh-CN,zh;q=0.9",
            "Cache-Control": "max-age=0",
            "Connection": "keep-alive",
            "Host": "ip.42.pl",
            "sec-ch-ua": '" Not A;Brand";v="99", "Chromium";v="101", "Google Chrome";v="101"',
            "sec-ch-ua-mobile": "?0",
            "sec-ch-ua-platform": '"Windows"',
            "Sec-Fetch-Dest": "document",
            "Sec-Fetch-Mode": "navigate",
            "Sec-Fetch-Site": "none",
            "Sec-Fetch-User": "?1",
            "Upgrade-Insecure-Requests": "1",
            "User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/101.0.4951.54 Safari/537.36"
        }
        reql = requests.get(url='https://ip.42.pl/', headers=headers)
        reql_html = reql.text
        req_ip = re.findall("<p class=\"ip\">(.*?)</p>", reql_html, re.S)[0]
        return req_ip, self.Get_html(req_ip)

    def Get_html(self, IP_num):
        headers = {
            "Accept": "text/html,application/xhtml+xml,application/xml;q=0.9,image/avif,image/webp,image/apng,*/*;q=0.8,application/signed-exchange;v=b3;q=0.9",
            "Accept-Encoding": "gzip, deflate, br",
            "Accept-Language": "zh-CN,zh;q=0.9",
            "Connection": "keep-alive",
            "Cookie": f"Hm_lvt_f4f76646cd877e538aa1fbbdf351c548={int(time.time())}; ASPSESSIONIDAACCRABA=CBKOIKKCNDIIPAOOGMCDFCBF; Hm_lpvt_f4f76646cd877e538aa1fbbdf351c548={int(time.time())}; PHPSESSID=q1nkv8g7dj6i9isu55gk8nl7ek",
            "Host": "www.ip138.com",
            "Referer": f"https://www.ip138.com/iplookup.asp?ip={IP_num}&action=2",
            "sec-ch-ua": '" Not A;Brand";v="99", "Chromium";v="98", "Google Chrome";v="98"',
            "sec-ch-ua-mobile": "?0",
            "sec-ch-ua-platform": '"macOS"',
            "Sec-Fetch-Dest": "document",
            "Sec-Fetch-Mode": "navigate",
            "Sec-Fetch-Site": "same-origin",
            "Sec-Fetch-User": "?1",
            "Upgrade-Insecure-Requests": "1",
            "User-Agent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/98.0.4758.109 Safari/537.36"
        }
        payload = {
            "ip": IP_num,
            "action": 2
        }

        reql = requests.get(self.capture_ip, headers=headers, params=payload)
        reql.encoding = "GBK"

        IP_json = re.findall("ip_result = (.*?);", reql.text, re.S)
        json_IP_data = json.loads(IP_json[0])
        return_data = {
            "ct": json_IP_data["ip_c_list"][0]["ct"],
            "prov": json_IP_data["ip_c_list"][0]["prov"],
            "city": json_IP_data["ip_c_list"][0]["city"],
            "area": json_IP_data["ip_c_list"][0]["area"],
            "yunyin": json_IP_data["ip_c_list"][0]["yunyin"],
            "IP_address": json_IP_data["ASN归属地"].strip()
        }

        return return_data


def hanzi_to_pinyin(last_name):
    """
    功能说明：将姓名转换为拼音首字母缩写
    参数：hanzi_to_pinyin(u'李白')
    返回值：lb
    """
    rows = pypinyin.pinyin(last_name, style=pypinyin.NORMAL)  # 获取姓氏首字母
    rows_list = [row[0][0] for row in rows if len(row) > 0]  # 拼接姓名首字母字符串
    return rows_list[0].upper()


def createPagePdf(num, tmp):
    c = canvas.Canvas(tmp)
    for i in range(1, num + 1):
        c.drawString((210 // 2) * mm, (4) * mm, str(i))
        c.showPage()
    c.save()


def add_page_numgers(pdf_path):
    """
    Add page numbers to a pdf, save the result as a new pdf
    @param pdf_path: path to pdf
    """

    tmp = "__tmp.pdf"

    output = PdfFileWriter()
    with open(pdf_path, 'rb') as f:
        pdf = PdfFileReader(f, strict=False)
        n = pdf.getNumPages()
        print(n)
        # create new PDF with page numbers

        createPagePdf(n, tmp)

        with open(tmp, 'rb') as ftmp:
            numberPdf = PdfFileReader(ftmp)
            # iterarte pages
            for p in range(n):
                page = pdf.getPage(p)
                numberLayer = numberPdf.getPage(p)
                # merge number page with actual page
                page.mergePage(numberLayer)
                output.addPage(page)

            # write result
            if output.getNumPages():
                newpath = pdf_path.replace("_numbered", "")
                print(newpath)
                with open(newpath, 'wb') as f:
                    output.write(f)
        os.remove(tmp)
    os.remove(pdf_path)


# 第三步：定义相对应的函数
def get_num_pages(file_path):
    """
    获取文件总页码
    :param file_path: 文件路径
    :return:
    """
    try:
        with open(file_path, 'rb') as f:
            pdf = PdfFileReader(f, strict=False)
            return pdf.getNumPages()
    except:
        return 0

# 图片添加水印
def add_watermark(picture_path):
    """
    :param picture_path: 需要加水印的图片目录
    :return:
    """
    # 加水印后保存目录
    retpath = os.getcwd() + "/media/seal_dir"
    '''add_mark是图片加水印的方法，共有8个参数'''
    # file：图片文件或图片文件夹路径
    # out：添加水印后的结果保存位置，默认生成到 output 文件夹
    # mark：要添加的水印内容
    # opacity：水印的透明度，默认 0.15
    # angle：水印旋转角度，默认 30 度
    # space：水印直接的间隔, 默认 75 个空格
    # size：水印字体的大小，默认 50
    # color：文字水印颜色设置 16进制
    print("你好")
    # 水印测试一
    add_mark(file=picture_path, out=retpath, mark="京山市人民医院", opacity=0.4, angle=35, space=75, size=80, color='#A9A9A9')
    return retpath.replace(os.getcwd(), "")


class my_shouwen():
    def my_yishou(self, datavalue, search_data, request, page, page_size):
        print("datavalue: ", datavalue)
        if datavalue:
            if datavalue != "z" and datavalue != "w" and datavalue != "y":  # 内部发文
                inbox = request.user.conferencememopeople_set.filter(Q(notice_id__conference_memo_text__contains=search_data) |
                                                              Q(notice_id__document_number__contains=search_data) |
                                                              Q(notice_id__drafter__contains=search_data) |
                                                              Q(notice_id__drafter_company__contains=search_data),state='accepted',
                                                              audit_people=request.user, notice_id__type=datavalue).order_by("-update_time")
            elif datavalue == "w":
                inbox = request.user.correspondencepeople_set.filter(Q(notice_id__title__contains=search_data) |
                                                                        Q(notice_id__recive_number__contains=search_data) |
                                                                        Q(notice_id__apply_user__real_name__contains=search_data) |
                                                                        Q(notice_id__apply_user__department__contains=search_data),
                                                                        state='accepted', audit_people=request.user).order_by("-update_time")
            elif datavalue == "y":
                inbox = request.user.committeeminutespeople_set.filter(Q(parent_id__conference_memo_text__contains=search_data) |
                                                                        Q(parent_id__document_number__contains=search_data) |
                                                                        Q(parent_id__apply_user__real_name__contains=search_data) |
                                                                        Q(parent_id__apply_user__company__name__contains=search_data),
                                                                        state='accepted', audit_people=request.user).order_by("-update_time")
            else:  # 上级来文
                inbox = request.user.officialdocumentspeople_set.filter(Q(notice_id__title__contains=search_data) |
                                                                 Q(notice_id__recive_number__contains=search_data) |
                                                                 Q(notice_id__apply_user__real_name__contains=search_data) |
                                                                 Q(notice_id__apply_user__department__contains=search_data),
                                                                 state='accepted', audit_people=request.user).order_by("-update_time")

        else:
            q1 = request.user.officialdocumentspeople_set.filter(Q(notice_id__title__contains=search_data) |
                                                                 Q(notice_id__recive_number__contains=search_data) |
                                                                 Q(notice_id__apply_user__real_name__contains=search_data) |
                                                                 Q(notice_id__apply_user__department__contains=search_data),
                                                                 state='accepted').order_by("-update_time")
            a2 = request.user.conferencememopeople_set.filter(Q(notice_id__conference_memo_text__contains=search_data) |
                                                              Q(notice_id__document_number__contains=search_data) |
                                                              Q(notice_id__drafter__contains=search_data) |
                                                              Q(notice_id__drafter_company__contains=search_data),
                                                              state='accepted').order_by("-update_time")
            q2 = request.user.correspondencepeople_set.filter(Q(notice_id__title__contains=search_data) |
                                                                 Q(notice_id__recive_number__contains=search_data) |
                                                                 Q(notice_id__apply_user__real_name__contains=search_data) |
                                                                 Q(notice_id__apply_user__department__contains=search_data),
                                                                 state='accepted').order_by("-update_time")
            q3 = request.user.committeeminutespeople_set.filter(Q(parent_id__conference_memo_text__contains=search_data) |
                                                                Q(parent_id__document_number__contains=search_data) |
                                                                Q(parent_id__apply_user__real_name__contains=search_data) |
                                                                Q(parent_id__apply_user__company__name__contains=search_data),
                                                                state='accept').order_by("-update_time")

            inbox = sorted(chain(q1, a2, q2, q3), key=attrgetter('update_time'), reverse=True)
        data_list = []
        for i in inbox[(int(page) - 1) * int(page_size):int(page) * int(page_size)]:
            try:
                aaa = i.real_name
                types = i.notice_id.type
                title = i.notice_id.conference_memo_text
                document_number = i.notice_id.document_number
                doc_url = "document_detail.html?attention=1&receiving=1&id=" + str(i.notice_id.id)
                if i.notice_id.drafter:
                    real_name = i.notice_id.drafter
                    department = i.notice_id.drafter_company
                else:
                    real_name = i.notice_id.apply_user.real_name
                    department = i.notice_id.apply_user.department
            except Exception as E:
                if datavalue == "w":
                    title = i.notice_id.title
                    types = "w"
                    document_number = i.notice_id.recive_number
                    real_name = i.notice_id.apply_user.real_name
                    department = i.notice_id.apply_user.department
                    doc_url = "ExternalCorrespondenceDetail.html?id=" + str(i.notice_id.id)
                else:
                    try:
                        title = i.parent_id.conference_memo_text
                        types = "y"
                        document_number = i.parent_id.document_number
                        real_name = i.parent_id.apply_user.real_name
                        department = i.parent_id.apply_user.company.name
                        doc_url = "/supervision-inspection/summary_application_detail.html?id=" + str(i.parent_id.id)
                    except:
                        title = i.notice_id.title
                        types = "z"
                        document_number = i.notice_id.recive_number
                        real_name = i.notice_id.apply_user.real_name
                        department = i.notice_id.apply_user.department
                        doc_url = "examinationDetail.html?id=" + str(i.notice_id.id)
            try:
                one_dict = {'id': i.parent_id.id, "title": title, "real_name": real_name, "department": department,
                            "create_time": i.update_time.strftime("%Y-%m-%d %H:%M"), "document_number": document_number,
                            'url': doc_url, "text": "收阅", "types": types}
            except:
                one_dict = {'id': i.notice_id.id, "title": title, "real_name": real_name, "department": department,
                            "create_time": i.update_time.strftime("%Y-%m-%d %H:%M"), "document_number": document_number,
                            'url': doc_url, "text": "收阅", "types": types}
            data_list.append(one_dict)
        return len(inbox), data_list

    def my_daishou(self, datavalue, search_data, request, page, page_size):
        if datavalue:
            if datavalue != "z" and datavalue != "w" and datavalue != "y":  # 内部发文
                inbox = request.user.conferencememopeople_set.filter(Q(notice_id__conference_memo_text__contains=search_data) |
                                                              Q(notice_id__document_number__contains=search_data) |
                                                              Q(notice_id__drafter__contains=search_data) |
                                                              Q(notice_id__drafter_company__contains=search_data), state='accept',
                                                              notice_id__type=datavalue).order_by("-update_time")
            elif datavalue == "w":
                inbox = request.user.correspondencepeople_set.filter(Q(notice_id__title__contains=search_data) |
                                                                        Q(notice_id__recive_number__contains=search_data) |
                                                                        Q(notice_id__apply_user__real_name__contains=search_data) |
                                                                        Q(notice_id__apply_user__department__contains=search_data),
                                                                        state='accept', audit_people=request.user).order_by("-update_time")
            elif datavalue == "y":
                inbox = request.user.committeeminutespeople_set.filter(Q(parent_id__conference_memo_text__contains=search_data) |
                                                                        Q(parent_id__document_number__contains=search_data) |
                                                                        Q(parent_id__apply_user__real_name__contains=search_data) |
                                                                        Q(parent_id__apply_user__company__name__contains=search_data),
                                                                        state='accept', audit_people=request.user).order_by("-update_time")
                for i in inbox:
                    print(i.parent_id.conference_memo_text)
            else:  # 上级来文
                inbox = request.user.officialdocumentspeople_set.filter(Q(notice_id__title__contains=search_data) |
                                                                 Q(notice_id__recive_number__contains=search_data) |
                                                                 Q(notice_id__apply_user__real_name__contains=search_data) |
                                                                 Q(notice_id__apply_user__department__contains=search_data),
                                                                 state='accept', audit_people=request.user).order_by("-update_time")

        else:
            q1 = request.user.officialdocumentspeople_set.filter(Q(notice_id__title__contains=search_data) |
                                                                 Q(notice_id__recive_number__contains=search_data) |
                                                                 Q(notice_id__apply_user__real_name__contains=search_data) |
                                                                 Q(notice_id__apply_user__department__contains=search_data),
                                                                 state='accept').order_by("-update_time")
            a2 = request.user.conferencememopeople_set.filter(Q(notice_id__conference_memo_text__contains=search_data) |
                                                              Q(notice_id__document_number__contains=search_data) |
                                                              Q(notice_id__drafter__contains=search_data) |
                                                              Q(notice_id__drafter_company__contains=search_data),
                                                              state='accept').order_by("-update_time")
            q2 = request.user.correspondencepeople_set.filter(Q(notice_id__title__contains=search_data) |
                                                              Q(notice_id__recive_number__contains=search_data) |
                                                              Q(notice_id__apply_user__real_name__contains=search_data) |
                                                              Q(notice_id__apply_user__department__contains=search_data),
                                                              state='accept').order_by("-update_time")
            q3 = request.user.committeeminutespeople_set.filter(Q(parent_id__conference_memo_text__contains=search_data) |
                                                              Q(parent_id__document_number__contains=search_data) |
                                                              Q(parent_id__apply_user__real_name__contains=search_data) |
                                                              Q(parent_id__apply_user__company__name__contains=search_data),
                                                              state='accept').order_by("-update_time")

            inbox = sorted(chain(q1, a2, q2, q3), key=attrgetter('update_time'), reverse=True)
        data_list = []
        for i in inbox[(int(page) - 1) * int(page_size):int(page) * int(page_size)]:
            try:
                aaa = i.real_name
                title = i.notice_id.conference_memo_text
                document_number = i.notice_id.document_number
                doc_url = "document_detail.html?attention=1&receiving=1&id=" + str(i.notice_id.id)
                if i.notice_id.drafter:
                    real_name = i.notice_id.drafter
                    department = i.notice_id.drafter_company
                else:
                    real_name = i.notice_id.apply_user.real_name
                    department = i.notice_id.apply_user.department
                types = i.notice_id.type
            except:
                try:
                    flow_type = i.notice_id.flow_type
                    title = i.notice_id.title
                    types = "w"
                    document_number = i.notice_id.recive_number
                    real_name = i.notice_id.apply_user.real_name
                    department = i.notice_id.apply_user.department
                    doc_url = "ExternalCorrespondenceDetail.html?id=" + str(i.notice_id.id)
                except:
                    try:
                        title = i.parent_id.conference_memo_text
                        types = "y"
                        document_number = i.parent_id.document_number
                        real_name = i.parent_id.apply_user.real_name
                        department = i.parent_id.apply_user.company.name
                        doc_url = "/supervision-inspection/summary_application_detail.html?id=" + str(i.parent_id.id)
                    except:
                        title = i.notice_id.title
                        types = "z"
                        document_number = i.notice_id.recive_number
                        real_name = i.notice_id.apply_user.real_name
                        department = i.notice_id.apply_user.department
                        doc_url = "examinationDetail.html?id=" + str(i.notice_id.id)
            try:
                one_dict = {'id': i.parent_id.id, "title": title, "real_name": real_name, "department": department,
                        "create_time": i.update_time.strftime("%Y-%m-%d %H:%M"), "document_number": document_number,
                        'url': doc_url, "text": "收阅", "types": types}
            except:
                one_dict = {'id': i.notice_id.id, "title": title, "real_name": real_name, "department": department,
                            "create_time": i.update_time.strftime("%Y-%m-%d %H:%M"), "document_number": document_number,
                            'url': doc_url, "text": "收阅", "types": types}
            data_list.append(one_dict)
        return len(inbox), data_list


# 首页我的待办
def my_home_need_done(user_id, user_name):
    select_sql = f"""
            SELECT * from (SELECT
                    '车辆申请' as classification,
                a.id as id,
                a.create_time as create_time,
                u.real_name as apply_user,
                c.`name` as company,
                    case when a.state = 'draft' then '审核中'
                     when a.state = 'fg' then '审核中'
                     when a.state = 'ybzr' then '审核中'
                     when a.state = 'cgb' then '完结'
                     when a.state = 'done' then '完结'
                     when a.state = 'refuse' then '退回'
                     when a.state = 'cancel' then '作废'
                END as state,
                au.real_name as audit_user,
                a.update_time as audit_time,
                a.`subject` as title,
                a.state as st
                from administrative_vehicleapply a
                INNER JOIN tb_users_message u on a.apply_user_id = u.id
                INNER JOIN administrative_company c on u.company_id = c.id
                LEFT JOIN tb_users_message au on au.id = a.current_audit_user_id
                where a.current_audit_user_id = {user_id}
            union all
            SELECT
            '通知公告' as classification,
            u.id as id,
            a.create_time as create_time,
            c.real_name as apply_user,
            c.department as company,
                            case when a.state = 'draft' then '退回'
                             when a.state = 'audit' then '完结'
                             when a.state = 'unaudit' then '审核中'
                    END as state,
                    au.real_name as audit_user,
                    u.update_time as audit_time,
                    u.`name` as title,
                    a.state as st
                    from announcements_auditrecord	a
                    INNER JOIN announcements_notice u on a.notice_id_id = u.id
                    INNER JOIN tb_users_message c on u.create_people_id = c.id
                    LEFT JOIN tb_users_message au on au.id = u.audit_people_id
                    where u.audit_people_id = {user_id} AND a.state='unaudit'
            union all
            SELECT
            (case when a.type = 'db' then '党委会议题'
             when a.type = 'yb' then '院长办公会议题'
             when a.type = 'xz' then '行政例会议题'
        END) as classification,
                a.id as id,
                a.create_time as create_time,
                u.real_name as apply_user,
                c.`name` as company,
                case when a.state = 'draft' then '审核中'
                     when a.state = 'confirm' then '审核中'
                     when a.state = 'audit' then '审核中'
                     when a.state = 'done' then '完结'
                     when a.state = 'refuse' then '退回'
                     when a.state = 'cancel' then '作废'
                     when a.state = 'stop' then '终止'
                END as state,
                a.current_audit_user as audit_user,
                a.update_time as audit_time,
                a.topics_name as title,
                a.state as st
            FROM administrative_topics a
            INNER JOIN tb_users_message u on a.employee_id = u.id
            INNER JOIN administrative_company c on u.company_id = c.id
            where a.current_audit_user = '{user_name}'
            union all
            SELECT
            (case when a.type = 'a' then '党委会议纪要'
             when a.type = 'b' then '院长办公会会议纪要'
             when a.type = 'c' then '党委专题会议纪要'
             when a.type = 'd' then '院长办公会专题会议纪要'
             when a.type = 'e' then '行政例会简报'
             when a.type = 'f' then '行政公文(京医发)'
             when a.type = 'g' then '党委公文'
                             when a.type = 'h' then '行政公文(京医文)'
        END) as classification,
                a.id as id,
                a.create_time as create_time,
                u.real_name as apply_user,
                c.`name` as company,
                case when a.state = 'one' then '审核中'
                     when a.state = 'two' then '审核中'
                     when a.state = 'three' then '审核中'
                     when a.state = 'four' then '审核中'
                     when a.state = 'five' then '审核中'
                     when a.state = 'six' then '退回'
                     when a.state = 'seven' then '完结'
                     when a.state = 'eight' then '待分发'
                     when a.state = 'nine' then '审核中'
                     when a.state = 'stop' then '终止'
                END as state,
                GROUP_CONCAT(DISTINCT au.real_name SEPARATOR ',') as audit_user,
                a.update_time as audit_time,
                a.conference_memo_text as title,
                a.state as st
            FROM administrative_conferencememo a
            INNER JOIN tb_users_message u on a.apply_user_id = u.id
            INNER JOIN administrative_company c on u.company_id = c.id
            left join administrative_conferencememo_current_audit_users m on m.conferencememo_id = a.id
            LEFT JOIN tb_users_message au on m.user_id = au.id
            where au.id = {user_id}
            GROUP BY classification, id, create_time, apply_user, company, state
            union all
            SELECT
            (case when a.type = 'a' then '党委会议纪要(收阅)'
             when a.type = 'b' then '院长办公会会议纪要(收阅)'
             when a.type = 'c' then '党委专题会议纪要(收阅)'
             when a.type = 'd' then '院长办公会专题会议纪要(收阅)'
             when a.type = 'e' then '行政例会简报(收阅)'
             when a.type = 'f' then '京医发(收阅)'
             when a.type = 'g' then '党委公文(收阅)'
                             when a.type = 'h' then '京医文(收阅)'
        END) as classification,
                a.id as id,
                a.create_time as create_time,
                u.real_name as apply_user,
                c.`name` as company,
                '传阅中' as state,
                '' as audit_user,
                a.update_time as audit_time,
                a.conference_memo_text as title,
                a.state as st
            FROM administrative_conferencememo a
            INNER JOIN tb_users_message u on a.apply_user_id = u.id
            INNER JOIN administrative_company c on u.company_id = c.id
            left join administrative_conferencememopeople m on m.notice_id_id = a.id
            LEFT JOIN tb_users_message au on m.audit_people_id = au.id
            where au.id = {user_id} AND m.state='accept'
            GROUP BY classification, id, create_time, apply_user, company, state
            union all
            SELECT
            '上级来文(收阅)' as classification,
            a.id as id,
            a.create_time as create_time,
            u.real_name as apply_user,
            c.`name` as company,
            '传阅中' as state,
            '' as audit_user,
            a.update_time as audit_time,
            a.title as title,
            a.state as st
            FROM administrative_officialdocuments a
            INNER JOIN tb_users_message u on a.apply_user_id = u.id
            INNER JOIN administrative_company c on u.company_id = c.id
            left join administrative_officialdocumentspeople m on m.notice_id_id = a.id
            LEFT JOIN tb_users_message au on m.audit_people_id = au.id
            where au.id = {user_id} AND m.state='accept'
            GROUP BY classification, id, create_time, apply_user, company, state
            union all
            SELECT
            '上级来文' as classification,
            a.id as id,
            a.create_time as create_time,
            u.real_name as apply_user,
            c.`name` as company,
            case when a.type = 'a' then '审核中'
                     when a.type = 'b' then '审核中'
                     when a.type = 'c' then '审核中'
                     when a.type = 'd' then '传阅中'
                     when a.type = 'e' then '完结'
                     when a.type = 'f' then '退回'
                     when a.type = 'g' then '待分发'
                END as state,
            GROUP_CONCAT(DISTINCT au.real_name SEPARATOR ',') as audit_user,
            a.update_time as audit_time,
            a.title as title,
            a.state as st
            FROM administrative_officialdocuments a
            INNER JOIN tb_users_message u on a.apply_user_id = u.id
            INNER JOIN administrative_company c on u.company_id = c.id
            left join administrative_officialdocuments_current_audit_users m on m.officialdocuments_id = a.id
            LEFT JOIN tb_users_message au on m.user_id = au.id
            where au.id = {user_id}
            GROUP BY classification, id, create_time, apply_user, company, state
            union all
            SELECT
            '用印申请' as classification,
                a.id as id,
                a.create_time as create_time,
                u.real_name as apply_user, 
                c.`name` as company,
                case when a.state = 'five' then '完结'
                        when a.state = 'six' then '退回'
                        else '审核中'
                        END as state,
                GROUP_CONCAT(DISTINCT au.real_name SEPARATOR ',') as audit_user,
                a.update_time as audit_time,
                a.matter_type_text as title,
                a.state as st
            from personal_center_sealapply a
            INNER JOIN tb_users_message u on a.apply_user_id = u.id
            INNER JOIN administrative_company c on u.company_id = c.id
            left join personal_center_sealapply_current_audit_users m on m.sealapply_id = a.id
            LEFT JOIN tb_users_message au on m.user_id = au.id
            where au.id = {user_id}
            GROUP BY classification, id, create_time, apply_user, company, state
            union all
            SELECT 
                '会议室申请'   as classification,
                a.id as id,
                a.create_time as create_time,
                a.names as apply_user, 
                a.department as company,
                case when a.state = 'audit' then '完结'
                                                else '审核中'
                                                END as state,
                u.real_name as audit_user,
                a.update_time as audit_time,
                a.meeting_root as title,
                a.state as st
                FROM personal_center_conferenceroomapplication a
                left join tb_users_message u on a.audit_people_id = u.id
                where a.audit_people_id = {user_id} and status != '3'
                GROUP BY classification, id, create_time, apply_user, company, state
                union all
            SELECT
            '固定资产付款详情' as classification,
                a.id as id,
                a.create_time as create_time,
                u.real_name as apply_user, 
                c.`name` as company,
                case when a.state = 'six' then '完结'
                        when a.state = 'seven' then '退回'
                        else '审核中'
                        END as state,
                GROUP_CONCAT(DISTINCT au.real_name SEPARATOR ',') as audit_user,
                a.update_time as audit_time,
                a.title as title,
                a.state as st
            from personal_center_fixedassets a
            INNER JOIN tb_users_message u on a.apply_user_id = u.id
            INNER JOIN administrative_company c on u.company_id = c.id
            left join personal_center_fixedassets_current_audit_users m on m.fixedassets_id = a.id
            LEFT JOIN tb_users_message au on m.user_id = au.id
            where au.id = {user_id}
            GROUP BY classification, id, create_time, apply_user, company, state
            union all
                SELECT
                '请假申请' as classification,
                    a.id as id,
                    a.create_time as create_time,
                    u.real_name as apply_user, 
                    c.`name` as company,
                    case when a.state = 'six' then '完结'
                            when a.state = 'seven' then '退回'
                            else '审核中'
                            END as state,
                    GROUP_CONCAT(DISTINCT au.real_name SEPARATOR ',') as audit_user,
                    a.update_time as audit_time,
                    a.reason as title,
                    a.state as st
                from finance_leaverequest a
                INNER JOIN tb_users_message u on a.apply_user_id = u.id
                INNER JOIN administrative_company c on u.company_id = c.id
                left join finance_leaverequest_current_audit_users m on m.leaverequest_id = a.id
                LEFT JOIN tb_users_message au on m.user_id = au.id
                where au.id = {user_id}
                GROUP BY classification, id, create_time, apply_user, company, state
            union all
                SELECT
                '维修维保服务项目' as classification,
                    a.id as id,
                    a.create_time as create_time,
                    u.real_name as apply_user, 
                    c.`name` as company,
                    case when a.state = 'six' then '完结'
                            when a.state = 'seven' then '退回'
                            else '审核中'
                            END as state,
                    GROUP_CONCAT(DISTINCT au.real_name SEPARATOR ',') as audit_user,
                    a.update_time as audit_time,
                    a.title as title,
                    a.state as st
                from finance_maintainserver a
                INNER JOIN tb_users_message u on a.apply_user_id = u.id
                INNER JOIN administrative_company c on u.company_id = c.id
                left join finance_maintainserver_current_audit_users m on m.maintainserver_id = a.id
                LEFT JOIN tb_users_message au on m.user_id = au.id
                where au.id = {user_id}
                GROUP BY classification, id, create_time, apply_user, company, state
            union all
                SELECT
                '离职申请' as classification,
                    a.id as id,
                    a.create_time as create_time,
                    u.real_name as apply_user, 
                    c.`name` as company,
                    case when a.state = 'six' then '完结'
                            when a.state = 'seven' then '退回'
                            else '审核中'
                            END as state,
                    GROUP_CONCAT(DISTINCT au.real_name SEPARATOR ',') as audit_user,
                    a.update_time as audit_time,
                    '离职申请' as title,
                    a.state as st
                from finance_dimission a
                INNER JOIN tb_users_message u on a.user_id = u.id
                INNER JOIN administrative_company c on u.company_id = c.id
                left join finance_dimission_current_audit_users m on m.dimission_id = a.id
                LEFT JOIN tb_users_message au on m.user_id = au.id
                where au.id = {user_id}
                GROUP BY classification, id, create_time, apply_user, company, state
            union all
        SELECT
            '医德医风考评'   as classification,
            a.id as id,
            a.create_time as create_time,
            u.real_name as apply_user, 
            c.`name` as company,
            case when a.state = 'five' then '完结'
                                            else '审核中'
                                            END as state,
            GROUP_CONCAT(DISTINCT au.real_name SEPARATOR ',') as audit_user,
            a.update_time as audit_time,
            a.title as title,
            a.state as st
            from medical_ethics_evaluationofmedicalethics a
            left join tb_users_message u on u.id = a.apply_user_id
            left join administrative_company c on a.apply_company_id = c.id
            left join medical_ethics_evaluationofmedicalethics_current_audit_users m on m.evaluationofmedicalethics_id = a.id
            left join tb_users_message au on au.id = m.user_id
            where  m.user_id = {user_id}
            GROUP BY classification, id, create_time, apply_user, company, state, st
            union all
            select
                b.module   as classification,
                a.id as id,
                a.create_time as create_time,
                u.real_name as apply_user, 
                c.`name` as company,
                case when a.state = 'two' then '完结'
                                when a.state = 'three' then '作废'
                                when a.state = 'four' then '退回'
                                else '审核中'
                                END as state,
                GROUP_CONCAT(DISTINCT au.real_name SEPARATOR ',') as audit_user,
                a.update_time as audit_time,
                b.name as title,
                a.state as st
            from system_setup_lowcodebill a
            left join system_setup_customerform b on a.customer_form_id = b.id
            left join tb_users_message u on u.id = a.apply_user_id
            left join administrative_company c on u.company_id = c.id
            left join system_setup_lowcodebill_current_audit_users m on m.lowcodebill_id = a.id
            LEFT JOIN tb_users_message au on au.id = m.user_id
            where m.user_id = {user_id}
            GROUP BY classification, id, create_time, apply_user, company, state, st
            union all
            SELECT 
                '差旅报销' as classification,
                a.id as id,
                a.create_time as create_time,
                a.applicat_id as apply_user, 
                a.applicat_id as company,
                case when a.state = 'audit' then '完结'
                                                else '审核中'
                                                END as state,
                u.id as audit_user,
                a.update_time as audit_time,
                a.business_matter as title,
                a.state as st
            FROM finance_travelreimbursement a
            left join finance_travelreimbursement_current_audit_users u on a.id = u.travelreimbursement_id
            where u.user_id = {user_id}
            GROUP BY classification, id, create_time, apply_user, company, state, st
            union all
            SELECT 
                '临床医师单独执业资格申请' as classification,
                a.id as id,
                a.create_time as create_time,
                a.apply_user_id as apply_user, 
                a.company_id as company,
                case when a.state = 'audit' then '完结'
                                                else '审核中'
                                                END as state,
                u.id as audit_user,
                a.update_time as audit_time,
                '临床医师单独执业资格申请' as title,
                a.state as st
            FROM finance_solopracticeapply a
            left join finance_solopracticeapply_current_audit_users u on a.id = u.solopracticeapply_id
            where u.user_id = {user_id}
            GROUP BY classification, id, create_time, apply_user, company, state, st
            union all
            SELECT 
                '手术分级授权审批' as classification,
                a.id as id,
                a.create_time as create_time,
                a.apply_user_id as apply_user, 
                a.company_id as company,
                case when a.state = 'audit' then '完结'
                                                else '审核中'
                                                END as state,
                u.id as audit_user,
                a.update_time as audit_time,
                '手术分级授权申请' as title,
                a.state as st
            FROM finance_surgicalgrading a
            left join finance_surgicalgrading_current_audit_users u on a.id = u.surgicalgrading_id
            where u.user_id = {user_id}
            GROUP BY classification, id, create_time, apply_user, company, state, st
            union all
            SELECT 
                '新增医疗服务项目审批' as classification,
                a.id as id,
                a.create_time as create_time,
                a.apply_user_id as apply_user, 
                a.company_id as company,
                case when a.state = 'audit' then '完结'
                                                else '审核中'
                                                END as state,
                u.id as audit_user,
                a.update_time as audit_time,
                '新增医疗服务项目申请' as title,
                a.state as st
            FROM finance_newmedicalservices a
            left join finance_newmedicalservices_current_audit_users u on a.id = u.newmedicalservices_id
            where u.user_id = {user_id}
            GROUP BY classification, id, create_time, apply_user, company, state, st
            union all
            SELECT 
                '审计科经济合同会签流程' as classification,
                a.id as id,
                a.create_time as create_time,
                a.apply_user_id as apply_user, 
                a.company_id as company,
                case when a.state = 'audit' then '完结'
                                                else '审核中'
                                                END as state,
                u.id as audit_user,
                a.update_time as audit_time,
                '审计科经济合同会签' as title,
                a.state as st
            FROM finance_businesscontract a
            left join finance_businesscontract_current_audit_users u on a.id = u.businesscontract_id
            where u.user_id = {user_id}
            GROUP BY classification, id, create_time, apply_user, company, state, st
            union all
            SELECT 
                '差旅申请' as classification,
                a.id as id,
                a.create_time as create_time,
                a.apply_user_id as apply_user, 
                a.company_id as company,
                case when a.state = 'audit' then '完结'
                                                else '审核中'
                                                END as state,
                u.id as audit_user,
                a.update_time as audit_time,
                '差旅申请' as title,
                a.state as st
            FROM finance_travelapply a
            left join finance_travelapply_current_audit_users u on a.id = u.travelapply_id
            where u.user_id = {user_id}
            GROUP BY classification, id, create_time, apply_user, company, state, st
            union all
            SELECT 
                '临床用血费用付款流程' as classification,
                a.id as id,
                a.create_time as create_time,
                a.apply_user_id as apply_user, 
                a.company_id as company,
                case when a.state = 'audit' then '完结'
                                                else '审核中'
                                                END as state,
                u.id as audit_user,
                a.update_time as audit_time,
                '临床用血费用付款申请' as title,
                a.state as st
            FROM finance_paywithblood a
            left join finance_paywithblood_current_audit_users u on a.id = u.paywithblood_id
            where u.user_id = {user_id}
            GROUP BY classification, id, create_time, apply_user, company, state, st
            union all
            SELECT 
                '常规付款' as classification,
                a.id as id,
                a.create_time as create_time,
                a.apply_user_id as apply_user, 
                a.company_id as company,
                case when a.state = 'audit' then '完结'
                                                else '审核中'
                                                END as state,
                u.id as audit_user,
                a.update_time as audit_time,
                '常规付款申请' as title,
                a.state as st
            FROM finance_generalfinance a
            left join finance_generalfinance_current_audit_users u on a.id = u.generalfinance_id
            where u.user_id = {user_id}
            GROUP BY classification, id, create_time, apply_user, company, state, st
            union all
            SELECT 
                '卫生材料付款审批' as classification,
                a.id as id,
                a.create_time as create_time,
                a.apply_user_id as apply_user, 
                a.company_id as company,
                case when a.state = 'audit' then '完结'
                                                else '审核中'
                                                END as state,
                u.id as audit_user,
                a.update_time as audit_time,
                '卫生材料付款审批' as title,
                a.state as st
            FROM finance_medicalmaterials a
            left join finance_medicalmaterials_current_audit_users u on a.id = u.medicalmaterials_id
            where u.user_id = {user_id}
            GROUP BY classification, id, create_time, apply_user, company, state, st
            union all
            SELECT 
                '总务科付款' as classification,
                a.id as id,
                a.create_time as create_time,
                a.apply_user_id as apply_user, 
                a.company_id as company,
                case when a.state = 'audit' then '完结'
                                                else '审核中'
                                                END as state,
                u.id as audit_user,
                a.update_time as audit_time,
                '总务科付款申请' as title,
                a.state as st
            FROM finance_logisticsoffice a
            left join finance_logisticsoffice_current_audit_users u on a.id = u.logisticsoffice_id
            where u.user_id = {user_id}
            GROUP BY classification, id, create_time, apply_user, company, state, st
            union all
            SELECT 
                '营养科餐费付款审批' as classification,
                a.id as id,
                a.create_time as create_time,
                a.apply_user_id as apply_user, 
                a.company_id as company,
                case when a.state = 'audit' then '完结'
                                                else '审核中'
                                                END as state,
                u.id as audit_user,
                a.update_time as audit_time,
                '营养科餐费付款申请' as title,
                a.state as st
            FROM finance_nutritionalmeals a
            left join finance_nutritionalmeals_current_audit_users u on a.id = u.nutritionalmeals_id
            where u.user_id = {user_id}
            GROUP BY classification, id, create_time, apply_user, company, state, st
            ) z
    """
    with connection.cursor() as cursor:
        cursor.execute(select_sql)
    daiban_data = cursor.fetchall()
    return daiban_data
